from datetime import datetime

import pandas as pd

from src.models import (
    BenkeRecord,
    BoshiRecord,
    FormerGraduateRecord,
    FreshGraduateRecord,
    ShuoshiRecord,
)
from src.utils.date_utils import parse_date

SHUOSHI_HEADERS = [
    "学号",
    "姓名",
    "是否港澳台学生",
    "学习方式",
]

BOSHI_HEADERS = [
    "学号",
    "姓名",
    "是否港澳台学生",
    "学习方式",
]

BENKE_HEADERS = [
    "学号",
    "姓名",
    "学习方式",
    "是否留学生",
    "是否港澳台学生",
]

FRESH_HEADERS = [
    "学号",
    "姓名",
    "学历",
    "专业",
    "届别",
    "单位名称",
    "单位地址",
    "生源所在地",
    "身份证号",
    "联系方式",
    "档案转递类型",
    "档案转递单位名称",
    "档案转递地址",
    "档案转递联系人/联系部门",
    "档案转递联系电话",
    "转递编号",
]

FORMER_HEADERS = [
    "学号",
    "姓名",
    "培养层次",
    "专业",
    "档案接收地址",
    "档案接收单位",
    "档案接收人",
    "档案接收人电话",
]

EMS_HEADERS = ["EMS"]


def import_benke_data(excel_path: str) -> list[BenkeRecord]:
    df = pd.read_excel(excel_path, keep_default_na=False, dtype=str)
    # 检查列名是否正确
    if not all(col in df.columns for col in BENKE_HEADERS):
        raise ValueError(f"Excel文件必须包含以下列：{', '.join(BENKE_HEADERS)}")
    # 转换为列表
    records = df.to_dict(orient="records")
    return [
        BenkeRecord(
            id=record["学号"],
            name=record["姓名"],
            education_level=record.get("培养层次", "本科"),
            learning_mode=record["学习方式"],
            college=record.get("学院", ""),
            major=record.get("专业", ""),
            class_name=record.get("班级名称", ""),
            class_number=record.get("班号", ""),
            is_international_student=record["是否留学生"],
            is_hac_student=record["是否港澳台学生"],
            phone_number=record.get("联系电话", ""),
            record_status=record.get("学籍最后状态", ""),
            political_status=record.get("政治面貌", ""),
            id_card_number=record.get("证件号码", ""),
            graduation_status=record.get("毕业状态", ""),
            archive_arrvial_status=record.get("到档情况", ""),
            archive_arrvial_time=parse_date(record.get("到档时间", "")),
            party_archive_lend_record=record.get("党员档案借出情况记录", ""),
            degree_archive_status=record.get("学位档案归档状态", ""),
            graduate_registration_form_status=record.get("毕业登记表状态", ""),
            transcript_status=record.get("成绩单状态", ""),
            archive_delivery_status=record.get("档案寄出情况", ""),
        )
        for record in records
    ]


def import_shuoshi_data(excel_path: str) -> list[ShuoshiRecord]:
    df = pd.read_excel(excel_path, keep_default_na=False, dtype=str)
    if not all(col in df.columns for col in SHUOSHI_HEADERS):
        raise ValueError(f"Excel文件必须包含以下列：{', '.join(SHUOSHI_HEADERS)}")
    # 转换为列表
    records = df.to_dict(orient="records")
    return [
        ShuoshiRecord(
            id=record["学号"],
            name=record["姓名"],
            education_level=record.get("培养层次", "硕士"),
            learning_mode=record["学习方式"],
            college=record.get("学院", ""),
            student_category=record.get("学生类别", ""),
            education_mode=record.get("培养模式", ""),
            is_hac_student=record["是否港澳台学生"],
            phone_number=record.get("联系电话", ""),
            political_status=record.get("政治面貌", ""),
            id_card_number=record.get("证件号码", ""),
            graduation_status=record.get("毕业状态", ""),
            archive_arrvial_status=record.get("到档情况", ""),
            archive_arrvial_time=parse_date(record.get("到档时间", "")),
            party_archive_lend_record=record.get("党员档案借出情况记录", ""),
            degree_archive_status=record.get("学位档案归档状态", ""),
            graduate_registration_form_status=record.get("毕业登记表状态", ""),
            archive_delivery_status=record.get("档案寄出情况", ""),
        )
        for record in records
    ]


def import_boshi_data(excel_path: str) -> list[BoshiRecord]:
    df = pd.read_excel(excel_path, keep_default_na=False, dtype=str)
    # 检查列名是否正确
    if not all(col in df.columns for col in BOSHI_HEADERS):
        raise ValueError(f"Excel文件必须包含以下列：{', '.join(BOSHI_HEADERS)}")
    # 转换为列表
    records = df.to_dict(orient="records")
    return [
        BoshiRecord(
            id=record["学号"],
            name=record["姓名"],
            education_level=record.get("培养层次", "博士"),
            learning_mode=record["学习方式"],
            college=record.get("学院", ""),
            student_category=record.get("学生类别", ""),
            education_mode=record.get("培养模式", ""),
            is_hac_student=record["是否港澳台学生"],
            phone_number=record.get("联系电话", ""),
            political_status=record.get("政治面貌", ""),
            id_card_number=record.get("证件号码", ""),
            graduation_status=record.get("毕业状态", ""),
            archive_arrvial_status=record.get("到档情况", ""),
            archive_arrvial_time=parse_date(record.get("到档时间", "")),
            party_archive_lend_record=record.get("党员档案借出情况记录", ""),
            degree_archive_status=record.get("学位档案归档状态", ""),
            graduate_registration_form_status=record.get("毕业登记表状态", ""),
            archive_delivery_status=record.get("档案寄出情况", ""),
        )
        for record in records
    ]


def import_fresh_data(excel_path: str) -> list[FreshGraduateRecord]:
    df = pd.read_excel(excel_path, keep_default_na=False, dtype=str)
    # 检查列名是否正确
    if not all(col in df.columns for col in FRESH_HEADERS):
        raise ValueError(f"Excel文件必须包含以下列：{', '.join(FRESH_HEADERS)}")
    # 转换为列表
    records = df.to_dict(orient="records")
    return [
        FreshGraduateRecord(
            id=record["学号"],
            name=record["姓名"],
            major=record["专业"],
            graduate_at=record["届别"],
            employer=record["单位名称"],
            employer_address=record["单位地址"],
            student_origin_address=record["生源所在地"],
            student_id_card_number=record["身份证号"],
            student_phone_number=record["联系方式"],
            archives_recv_type=record["档案转递类型"],
            archives_recv_number=record["转递编号"],
            archives_recv_unit_name=record["档案转递单位名称"],
            archives_recv_address=record["档案转递地址"],
            archives_recvier=record["档案转递联系人/联系部门"],
            archives_recvier_phone_number=record["档案转递联系电话"],
            education_level=record["学历"],
            send_time=parse_date(record.get("寄出时间", "")) or datetime.now(),
            remark=record.get("备注", ""),
        )
        for record in records
    ]


def import_former_data(excel_path: str) -> list[FormerGraduateRecord]:
    df = pd.read_excel(excel_path, keep_default_na=False, dtype=str)
    # 检查列名是否正确
    if not all(col in df.columns for col in FORMER_HEADERS):
        raise ValueError(f"Excel文件必须包含以下列：{', '.join(FORMER_HEADERS)}")
    records = df.to_dict(orient="records")
    return [
        FormerGraduateRecord(
            id=record["学号"],
            name=record["姓名"],
            major=record["专业"],
            archives_recv_unit_name=record["档案接收单位"],
            student_id_card_number=record.get("身份证号", ""),
            student_phone_number=record.get("联系方式", ""),
            archives_recv_address=record["档案接收地址"],
            archives_recvier=record["档案接收人"],
            archives_recvier_phone_number=record["档案接收人电话"],
            education_level=record["培养层次"],
            send_time=parse_date(record.get("寄出时间", "")) or datetime.now(),
            remark=record.get("备注", ""),
        )
        for record in records
    ]


def import_ems_data(excel_path: str) -> list:
    df = pd.read_excel(excel_path, keep_default_na=False, dtype=str)
    # 检查列名是否正确
    if not all(col in df.columns for col in EMS_HEADERS):
        raise ValueError(f"Excel文件必须包含以下列：{', '.join(EMS_HEADERS)}")
    records = df.to_dict(orient="list")
    return records["EMS"]


if __name__ == "__main__":
    # import_benke_data(
    #     excel_path="output/本科生字段.xls",
    # )
    # import_shuoshi_data(
    #     excel_path="output/硕士研究生字段.xls",
    # )
    # import_boshi_data(
    #     excel_path="output/博士研究生字段.xls",
    # )
    import_fresh_data(
        excel_path="output/data.xlsx",
    )
    # import_former_data(
    #     excel_path="output/data1.xlsx",
    # )
